package pwd.allen.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationEvent;
import org.springframework.context.ApplicationEventPublisher;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import pwd.allen.entity.Person;
import pwd.allen.exception.MyException;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @author pwd
 * @create 2018-11-28 22:51
 **/
@Repository
public class PersonDao {

    JdbcTemplate jdbcTemplate;

    private SimpleJdbcInsert addPerson;

    @Autowired
    public PersonDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;

        this.addPerson = new SimpleJdbcInsert(jdbcTemplate)
                .withTableName("db_user")
                //指定需要数据库自动生成主键的列名
                .usingGeneratedKeyColumns("id");
    }

    @Autowired
    private ApplicationEventPublisher eventPublisher;

    /**
     * 事务异常回滚规则
     * 在没指定回滚异常/不回滚异常的情况下：
     *      已检查的异常（除RuntimeException之外的Exception子类，包括Exception）不会回滚
     *      未检查异常（RuntimeException及子类、Error）默认会回滚
     *
     * @param person
     * @return
     */
//    @Transactional
    @Transactional(rollbackFor = Throwable.class, noRollbackFor = SQLException.class) //除了SQLException其他任何异常都回滚
    public int insert(Person person) throws MyException {
        //发布一个事件，用于测试@TransactionalEventListener
        eventPublisher.publishEvent(new MyTransactionEvent("我是个与事务相关的事件"));

        String sql = "INSERT INTO db_user(user_name,age) values (?, ?)";
        int rel = jdbcTemplate.update(sql, person.getUserName(), person.getAge());

        // 模拟执行sql后出错
        if ("MyException".equals(person.getUserName())) {
            // 结果：未指定回滚异常规则的话，不会回滚
            throw new MyException("这是自定义的已检查异常");
        } else if ("RuntimeException".equals(person.getUserName())) {
            // 结果：未指定回滚异常规则的话，会回滚
            throw new RuntimeException("这是未检查的异常");
        } else if ("thread".equals(person.getUserName())) {
            // 由于线程的本质，使得你不能捕获从线程中逃逸的异常。一旦异常逃逸出任务的run方法，它就会向外传播到控制台
            // 结果：不会回滚
            Thread thread = new Thread(new Runnable() {
                @Override
                public void run() {
                    System.out.println(jdbcTemplate.update(sql, "另一个线程", 12));
                    throw new RuntimeException("这是个需要回滚的异常");
                }
            });
            thread.start();
            try {
                thread.join();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }

        return rel;
    }

    /**
     * 使用SimpleJdbcInsert来插入数据，并返回自动生成的id
     * 介绍：SimpleJdbcInsert利用JDBC驱动所提供的数据库元数据的特性来简化操作配置
     *
     * @param person
     * @return
     */
    public Person add(Person person) {
        SqlParameterSource parameterSource = null;

        //方式一：bean，字段要相对应，否则传参是null
        parameterSource = new BeanPropertySqlParameterSource(person);

        //方式二：map
//        parameterSource = new MapSqlParameterSource("user_name", person.getUserName())
//                .addValue("age", person.getAge());

        Number key = addPerson.executeAndReturnKey(parameterSource);
        person.setId(key.intValue());
        return person;
    }

    /**
     * 使用jdbcTemplate查询单个记录
     * @param id
     * @return
     */
    public Person getById(Integer id) {

        RowMapper<Person> rowMapper = getRowMapper();

        //方法一
//        String sql = "select * from db_user where id=?";
//        return jdbcTemplate.queryForObject(sql, new Object[]{id}, rowMapper);

        //方法二：封装成NamedParameterJdbcTemplate，可以使用命名参数符号，还可以包装javaBean来作为参数来源
        String sql = "select * from db_user where id=:id";
        Person person = new Person();
        person.setId(id);
        return new NamedParameterJdbcTemplate(jdbcTemplate)
                .queryForObject(sql, new BeanPropertySqlParameterSource(person), rowMapper);

    }


    /**
     * 使用jdbcTemplate查询列表
     * @param ids
     * @return
     */
    public List<Person> getIds(Integer[] ids) {

        if (ids == null || ids.length == 0) {
            return null;
        }

        String sql = "select * from db_user where id in (?";
        for (int i = 1; i < ids.length; i++) {
            sql += ",?";
        }
        sql += ")";

        // 方法一：使用rowMapper
        RowMapper<Person> rowMapper = getRowMapper();
//        return jdbcTemplate.query(sql, ids, rowMapper);

        // 方法二：使用ResultSetExtractor
        // 整个resultset只回调一次
        ResultSetExtractor<List<Person>> resultSetExtractor = new ResultSetExtractor<List<Person>>() {
            @Override
            public List<Person> extractData(ResultSet rs) throws SQLException, DataAccessException {
                HashMap<Integer, Person> resultMap = new HashMap<>();
                while (rs.next()) {
                    Person person = null;
                    person = new Person();
                    person.setId(rs.getInt("id"));
                    person.setAge(rs.getInt("age"));
                    person.setUserName(rs.getString("user_name"));
                    resultMap.put(person.getId(), person);
                }
                return new ArrayList<Person>(resultMap.values());
            }
        };
        return jdbcTemplate.query(sql, ids, resultSetExtractor);
    }


    /**
     * 自定义一个事务事件
     */
    private static class MyTransactionEvent extends ApplicationEvent {
        public MyTransactionEvent(Object source) {
            super(source);
        }
    }

    /**
     * RowMapper，仅适合将一行映射到单个域对象，每一行记录都会回调一次
     * @return
     */
    RowMapper<Person> getRowMapper() {
        return new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                Person person = null;
                person = new Person();
                person.setId(rs.getInt("id"));
                person.setAge(rs.getInt("age"));
                person.setUserName(rs.getString("user_name"));
                return person;
            }
        };
    }
}
